CREATE                   procedure [dbo].[amsp_GetSearchableFiles]
    @InFileRootPath   varchar(255),
    @InWebRootPath    varchar(255),
    @InUseNewWindowFlag   char(1)='N',
    @InNewWindowWidth   int=500,
    @InNewWindowHeight    int=500,
    @InNewWindowToolbar   varchar(3)='no',
    @InNewWindowMenubar   varchar(3)='no',
    @InNewWindowStatusbar varchar(3)='no'

AS
BEGIN

  /*
  ** DESCRIPTION:
  ** ------------
  ** This sp returns the desired fields to be indexed for searching files
  **
  ** INPUTS:
  ** -------
  **   @InFileRootPath            -> Full path to root directory above Web root
  **   @InWebRootPath             -> Full path to the Web root
  **   @InUseNewWindowFlag        -> Should we use Content.OpenInNewWindowFlag
  **   @InNewWindowWidth          -> Width of new window
  **   @InNewWindowHeight         -> Height of new window
  **   @InNewWindowToolbar        -> Should new window have a toolbar (yes/no)
  **   @InNewWindowMenuBar        -> Should new window have a menubar (yes/no)
  **   @InNewWindowStatusBar      -> Should new window include the status bar (yes/no)
  **
  ** RESULT SET:
  ** -----------
  **   FilePath               -> Full Path to File to Index
  **   URL                    -> How to Display the Resulting File
  **   Title                  -> Display Title for File in Search Results
  **   InterestCategoryList   -> Comma Separated List of IC's
  **   CustomField            -> UserDefinedDate1 (Copyright Date)
  **
  ** NOTES:
  ** ------
  **
  ** PROGRAMMING CONSIDERATIONS:
  ** ---------------------------
  ** In General, the SQL used here is not all that fast ... this SP is
  ** designed to be used in batch as part of the daily re-indexing operation.
  ** It is not designed for real-time (i.e. while the user is waiting) use.
  **
  ** HISTORY:
  ** --------
  ** 06/01/2001  N.Malhotra   Initial Version Created
  ** 06/15/2001  N.Malhotra   Added Date and CustomField2
  ** 08/11/2001  N.Malhotra   Changed to use input parameters for File Paths and Admin Section
  **                          instead of querying application_variable table from within the proc.
  ** 09/04/2001  N.Malhotra   There are instances where PublishLocation is NULL or is the empty
  **                          string in content_file and content_html - added logic to deal with this.
  ** 09/27/2001  N.Malhotra   Fixed a bug where the InterestCategoryList for the
  **                          first matching content record was not being set properly.
  ** 10/22/2001  N.Malhotra   For MembersOnly content, changed logic to use the template specified
  **                          by the NavMenuID --- no longer hard-code to use template.cfm
  ** 10/25/2001  N.Malhotra   Clients are expecting that if they specify 'Open in New Window' then
  **                          this will even flow through to search results.  So, added input variables
  **                          to define the behavior and added code to use Content.OpenInNewWindowFlag.
  ** 11/30/2001  N.Malhotra   Fixed a bug that was setting the filepath for MembersOnly files incorrectly
  ** 10/02/2003  B. Harbin    Rewritten for use with the new advanced search engine.
  ** 10/14/2003  E.Tatsui     Changed to exclude admin website.
  **                          Changed UserDefinedField1 to PublicationDate
  **                          Added logic so that ContentFolders are dynamic link and Navigations are
  **                          linked to fused page.
  **                          Changed logic for directory structure to fit CM5\
  ** 05/04/2004  E.Tatsui     Added Nitin's change to use vCurrent_Content insead of content
  ** 01/30/2008  C.Robertson  Add "/" to @LeftNewWinString
  **                          Fixed bug where w.UseAspNetTemplateFlag = 'N' AND n.NavContentGroupInd = 'C' AND
  **                          c.OpenInNewWindowFlag = 'Y' AND @InUseNewWindowFlag = 'Y' AND isNULL(c.MembersOnlyFlag, 'N') != 'Y'
  **                          was going to an aspx page
  */

  Declare  
    @ComponentID    numeric,
    @OldComponentID   numeric,
    @InterestCategoryID   numeric,
    @InterestCategoryName   varchar(255),
    @InterestCategoryList   varchar(1000),
    @InterestCategoryNameList   varchar(2000),
@LeftNewWinString   varchar(512),
    @RightNewWinString    varchar(512),
    @l_FirstRow  int,
    @AdminWebsiteKey uniqueidentifier,
    @GECodePath varchar(255),
    @NavMenuID numeric,
    @WebsiteKey uniqueidentifier,
    @VirtualDirectoryPath varchar(255)

  /*
  ** Create a temp table to hold our result set
  */
  CREATE table #temp (
    ContentID     numeric,
    FilePath      varchar(255),
    URL       varchar(1000),
    Title     varchar(255),
    InterestCategoryList  varchar(1000),
    CustomField2    varchar(255),
    Keywords      varchar(1000),
    Description     varchar(2000),
    WebsiteKey    uniqueidentifier,
    AllWebsiteKeys varchar(1000),
    IncludeInCrossSiteSearchFlag char(1) Default('N'),
    NavContentGroupInd char(1),
    NavMenuID     numeric
  )

  SELECT @AdminWebsiteKey = Value
    FROM System_Variable
   WHERE Name = 'CMAdminWebsiteKey'

  SELECT @GECodePath = Value
    FROM System_Variable
   WHERE Name = 'GECodePath'

  SELECT @VirtualDirectoryPath = Value
    FROM System_Variable
   WHERE Name = 'VirtualDirectoryPath'

  IF Len(@GECodePath) > 1
    SET @GECodePath = RIGHT(@GECodePath,Len(@GECodePath)-1)
  ELSE IF @GECodePath = '/'
    SET @GECodePath = ''

  IF Len(@VirtualDirectoryPath) > 1
    SET @VirtualDirectoryPath = RIGHT(@VirtualDirectoryPath,Len(@VirtualDirectoryPath)-1)
  ELSE IF @VirtualDirectoryPath = '/'
    SET @VirtualDirectoryPath = ''
    
  /*
  ** If we are using Content.OpenInNewWindowFlag, then let's setup our string
  ** one time so it's not as messy in the SQL below
  */

  IF @InUseNewWindowFlag = 'Y' BEGIN
    SET @LeftNewWinString = '" target="_blank" onClick="newWin=open(''/'
    SET @RightNewWinString = ''', ''viewer'', ''width=' +
                             Convert(varchar(4), @InNewWindowWidth) +
                             ',height=' + Convert(varchar(4), @InNewWindowHeight) +
                             ',toolbar=' + @InNewWindowToolbar +
                             ',directories=no,menubar=' + + @InNewWindowMenubar +
                             ',status=' + @InNewWindowStatusbar +
                             ',resizable=yes,scrollbars=yes''); newWin.focus(); return false;"'
  END
  
  /*
  ** A little ugly, but for performance I don't want to cursor loop if I don't
  ** have to.  So, we'll do all the data manipulation in the query.  We are
  ** creating the complete FilePath a complete URL to be used direcly by verity.  
  */
  INSERT INTO #temp (ContentID, FilePath, URL, Title, Keywords, Description, CustomField2, WebsiteKey, IncludeInCrossSiteSearchFlag, NavContentGroupInd, NavMenuID)
    SELECT
      c.ContentID,
      FilePath = CASE
        WHEN c.MembersOnlyFlag = 'Y' THEN w.ProtectedRootPath + REPLACE(IsNULL(cf.PublishLocation, c.PublishLocation),'/','\')
        ELSE w.PublishRootPath + REPLACE(IsNull(cf.PublishLocation,c.PublishLocation),'/','\')
        END ,
      URL =  '"' +
       CASE WHEN n.NavContentGroupInd = 'C' THEN '/'
            WHEN c.SecureFlag = 'Y' THEN w.SecureWebsiteRootURL
            ELSE w.WebsiteRootURL END
      + CASE
        WHEN w.UseAspNetTemplateFlag = 'N' AND c.MembersOnlyFlag = 'Y' AND n.NavContentGroupInd = 'C'  AND c.OpenInNewWindowFlag = 'Y' AND @InUseNewWindowFlag = 'Y' THEN
           @GECodePath + 'TemplateRedirect.cfm?template=/MembersOnly.cfm&ContentID=' + convert(varchar(18), c.ContentID) + @LeftNewWinString + @GECodePath + 'TemplateRedirect.cfm?template=/MembersOnly.cfm&ContentID=' + convert(varchar(18), c.ContentID) + @RightNewWinString
        WHEN w.UseAspNetTemplateFlag = 'N' AND c.MembersOnlyFlag = 'Y' AND n.NavContentGroupInd = 'C'  THEN
           @GECodePath + 'TemplateRedirect.cfm?template=/MembersOnly.cfm&ContentID=' + convert(varchar(18), c.ContentID) + '"'
        WHEN w.UseAspNetTemplateFlag = 'N' AND c.MembersOnlyFlag = 'Y' AND c.OpenInNewWindowFlag = 'Y' AND @InUseNewWindowFlag = 'Y' THEN
           @GECodePath + 'Template.cfm?section=' + n.Name + '&template=/MembersOnly.cfm&ContentID=' + convert(varchar(18), c.ContentID) + @LeftNewWinString + @GECodePath + 'Template.cfm?section=' + n.Name + '&template=/MembersOnly.cfm&ContentID=' + convert(varchar(18), c.ContentID) + @RightNewWinString
        WHEN w.UseAspNetTemplateFlag = 'N' AND c.MembersOnlyFlag = 'Y' THEN
           @GECodePath + 'Template.cfm?section=' + n.Name + '&template=/MembersOnly.cfm&ContentID=' + convert(varchar(18), c.ContentID) + '"'
        WHEN w.UseAspNetTemplateFlag = 'N' AND n.NavContentGroupInd = 'C' AND c.OpenInNewWindowFlag = 'Y' AND @InUseNewWindowFlag = 'Y' AND isNULL(c.MembersOnlyFlag, 'N') != 'Y' THEN
           @GECodePath + 'TemplateRedirect.cfm?section=' + n.Name + '&template=/CM/ContentDisplay.cfm&ContentFileID=' + convert(varchar(18), cf.ContentFileID) + @LeftNewWinString + @GECodePath + 'TemplateRedirect.cfm?section=' + n.Name + '&template=/CM/ContentDisplay.cfm&ContentFileID=' + convert(varchar(18), cf.ContentFileID) + @RightNewWinString
        WHEN w.UseAspNetTemplateFlag = 'N' AND n.NavContentGroupInd = 'C' THEN
           @GECodePath + 'TemplateRedirect.cfm?section=' + n.Name + '&template=/CM/ContentDisplay.cfm&ContentFileID=' + convert(varchar(18), cf.ContentFileID) +  + '"'
        WHEN w.UseAspNetTemplateFlag = 'N' AND c.OpenInNewWindowFlag = 'Y' AND @InUseNewWindowFlag = 'Y' AND isNULL(c.MembersOnlyFlag, 'N') != 'Y' THEN
           REPLACE(ISNULL(cf.PublishLocation, c.PublishLocation), REPLACE(IsNull(w.NavPublishDirectory,''),'\','/'), IsNull(w.NavContentDisplayPath,''))           
           + @LeftNewWinString + REPLACE(ISNULL(cf.PublishLocation, c.PublishLocation), REPLACE(IsNull(w.NavPublishDirectory,''),'\','/'), IsNull(w.NavContentDisplayPath,'')) + @RightNewWinString  
        WHEN w.UseAspNetTemplateFlag = 'Y' AND c.MembersOnlyFlag = 'Y' AND n.NavContentGroupInd = 'C'  AND c.OpenInNewWindowFlag = 'Y' AND @InUseNewWindowFlag = 'Y' THEN
           @GECodePath + 'ContentManagerNet/TemplateRedirect.aspx?template=/ContentManagerNet/MembersOnly.aspx&ContentID=' + convert(varchar(18), c.ContentID) + @LeftNewWinString + @GECodePath + 'ContentManagerNet/TemplateRedirect.aspx?template=/ContentManagerNet/MembersOnly.aspx&ContentID=' + convert(varchar(18), c.ContentID) + @RightNewWinString
        WHEN w.UseAspNetTemplateFlag = 'Y' AND c.MembersOnlyFlag = 'Y' AND n.NavContentGroupInd = 'C'  THEN
           @GECodePath + 'ContentManagerNet/TemplateRedirect.aspx?template=/ContentManagerNet/MembersOnly.aspx&ContentID=' + convert(varchar(18), c.ContentID) + '"'
        WHEN w.UseAspNetTemplateFlag = 'Y' AND c.MembersOnlyFlag = 'Y' AND c.OpenInNewWindowFlag = 'Y' AND @InUseNewWindowFlag = 'Y' THEN
           @VirtualDirectoryPath + '/' + n.Name +  + '/AM/ContentManagerNet/MembersOnly.aspx?Section=' + n.Name + '&ContentID=' + convert(varchar(18), c.ContentID) + @LeftNewWinString + @VirtualDirectoryPath + '/' + n.Name +  + '/AM/ContentManagerNet/MembersOnly.aspx?Section=' + n.Name + '&ContentID=' + convert(varchar(18), c.ContentID) + @RightNewWinString
        WHEN w.UseAspNetTemplateFlag = 'Y' AND c.MembersOnlyFlag = 'Y' THEN
           @VirtualDirectoryPath + '/' + n.Name +  + '/AM/ContentManagerNet/MembersOnly.aspx?Section=' + n.Name + '&ContentID=' + convert(varchar(18), c.ContentID) + '"'
        WHEN w.UseAspNetTemplateFlag = 'Y' AND n.NavContentGroupInd = 'C' AND c.OpenInNewWindowFlag = 'Y' AND @InUseNewWindowFlag = 'Y' AND isNULL(c.MembersOnlyFlag, 'N') != 'Y' THEN
           @GECodePath + 'ContentManagerNet/TemplateRedirect.aspx?Section=' + n.Name + '&template=/ContentManagerNet/ContentDisplay.aspx&ContentFileID=' + convert(varchar(18), cf.ContentFileID) + @LeftNewWinString + @GECodePath + 'ContentManagerNet/TemplateRedirect.aspx?Section=' + n.Name + '&template=/ContentManagerNet/ContentDisplay.aspx&ContentFileID=' + convert(varchar(18), cf.ContentFileID) + @RightNewWinString
        WHEN w.UseAspNetTemplateFlag = 'Y' AND n.NavContentGroupInd = 'C' THEN
           @GECodePath + 'ContentManagerNet/TemplateRedirect.aspx?Section=' + n.Name + '&template=/ContentManagerNet/ContentDisplay.aspx&ContentFileID=' + convert(varchar(18), cf.ContentFileID) +  + '"'
        WHEN w.UseAspNetTemplateFlag = 'Y' AND c.OpenInNewWindowFlag = 'Y' AND @InUseNewWindowFlag = 'Y' AND isNULL(c.MembersOnlyFlag, 'N') != 'Y' THEN
           REPLACE(ISNULL(cf.PublishLocation, c.PublishLocation), REPLACE(IsNull(w.NavPublishDirectory,''),'\','/'), IsNull(w.NavContentDisplayPath,''))           
           + @LeftNewWinString + REPLACE(ISNULL(cf.PublishLocation, c.PublishLocation), REPLACE(IsNull(w.NavPublishDirectory,''),'\','/'), IsNull(w.NavContentDisplayPath,'')) + @RightNewWinString  
        ELSE
           REPLACE(ISNULL(cf.PublishLocation, c.PublishLocation), REPLACE(IsNull(w.NavPublishDirectory,''),'\','/'), IsNull(w.NavContentDisplayPath,'')) + '"'
      END,
      IsNULL(cf.Description, cf.FileName),
      c.Keywords,
      c.Description,
      convert(varchar(20), IsNULL(c.PublicationDate, '01/01/1950'), 102),
      w.WebsiteKey,
      w.IncludeInCrossSiteSearchFlag,
      n.NavContentGroupInd,
      n.NavMenuID
    FROM
      vCurrent_Content c, Nav_Menu n,
      Content_File cf, Website w
    WHERE c.WorkflowStatusCode = 'P'
      and c.NavMenuID = n.NavMenuID
      and n.WebsiteKey != @AdminWebsiteKey
      and c.ContentID = cf.ContentID
      and n.WebsiteKey = w.WebsiteKey
      and (LEN(RTrim(cf.PublishLocation)) > 0 OR LEN(RTrim(c.PublishLocation)) > 0)
      and cf.FileTypeCode not in ('JPG', 'GIF')
      AND IsNull(n.HideFlag,'N') = 'N'
    UNION
    SELECT
      c.ContentID,
      FilePath =
        CASE
        WHEN c.MembersOnlyFlag = 'Y' THEN w.ProtectedRootPath + REPLACE(IsNULL(ch.PublishLocation, c.PublishLocation),'/','\')
        ELSE w.PublishRootPath + REPLACE(IsNull(ch.PublishLocation,c.PublishLocation),'/','\')
        END ,
      URL =  '"' +
       CASE WHEN n.NavContentGroupInd = 'C' THEN '/'
            WHEN c.SecureFlag = 'Y' THEN w.SecureWebsiteRootURL
            ELSE w.WebsiteRootURL END
      + CASE
        WHEN w.UseAspNetTemplateFlag = 'N' AND c.MembersOnlyFlag = 'Y' AND n.NavContentGroupInd = 'C' AND c.OpenInNewWindowFlag = 'Y' AND @InUseNewWindowFlag = 'Y' THEN
           @GECodePath + 'TemplateRedirect.cfm?template=/MembersOnly.cfm&ContentID=' + convert(varchar(18), c.ContentID) + @LeftNewWinString + @GECodePath + 'TemplateRedirect.cfm?template=/MembersOnly.cfm&ContentID=' + convert(varchar(18), c.ContentID) + @RightNewWinString
        WHEN w.UseAspNetTemplateFlag = 'N' AND c.MembersOnlyFlag = 'Y' AND n.NavContentGroupInd = 'C' THEN
           @GECodePath + 'TemplateRedirect.cfm?template=/MembersOnly.cfm&ContentID=' + convert(varchar(18), c.ContentID) + '"'    
        WHEN w.UseAspNetTemplateFlag = 'N' AND c.MembersOnlyFlag = 'Y' AND c.OpenInNewWindowFlag = 'Y' AND @InUseNewWindowFlag = 'Y' THEN
           @GECodePath + 'Template.cfm?section=' + n.Name + '&template=/MembersOnly.cfm&ContentID=' + convert(varchar(18), c.ContentID) + @LeftNewWinString + @GECodePath + 'Template.cfm?section=' + n.Name + '&template=/MembersOnly.cfm&ContentID=' + convert(varchar(18), c.ContentID) + @RightNewWinString
        WHEN w.UseAspNetTemplateFlag = 'N' AND c.MembersOnlyFlag = 'Y' THEN
           @GECodePath + 'Template.cfm?section=' + n.Name + '&template=/MembersOnly.cfm&ContentID=' + convert(varchar(18), c.ContentID) + '"'
        WHEN w.UseAspNetTemplateFlag = 'N' AND n.NavContentGroupInd = 'C' AND c.OpenInNewWindowFlag = 'Y' AND @InUseNewWindowFlag = 'Y' AND isNULL(c.MembersOnlyFlag, 'N') != 'Y' THEN
           @GECodePath + 'TemplateRedirect.cfm?' + 'template=/CM/HTMLDisplay.cfm&ContentID=' + convert(varchar(18), c.ContentID) + @LeftNewWinString + @GECodePath + 'TemplateRedirect.cfm?'+ '?template=/HTMLDisplay.cfm&ContentID=' + convert(varchar(18), c.ContentID) + @RightNewWinString
        WHEN w.UseAspNetTemplateFlag = 'N' AND n.NavContentGroupInd = 'C' THEN
           @GECodePath + 'TemplateRedirect.cfm?' +'template=/CM/HTMLDisplay.cfm&ContentID=' + convert(varchar(18), c.ContentID) + '"'
        WHEN w.UseAspNetTemplateFlag = 'Y' AND c.MembersOnlyFlag = 'Y' AND n.NavContentGroupInd = 'C' AND c.OpenInNewWindowFlag = 'Y' AND @InUseNewWindowFlag = 'Y' THEN
           @GECodePath + 'ContentManagerNet/TemplateRedirect.aspx?template=/ContentManagerNet/MembersOnly.aspx&ContentID=' + convert(varchar(18), c.ContentID) + @LeftNewWinString + @GECodePath + 'ContentManagerNet/TemplateRedirect.aspx?template=/ContentManagerNet/MembersOnly.aspx&ContentID=' + convert(varchar(18), c.ContentID) + @RightNewWinString
        WHEN w.UseAspNetTemplateFlag = 'Y' AND c.MembersOnlyFlag = 'Y' AND n.NavContentGroupInd = 'C' THEN
           @GECodePath + 'ContentManagerNet/TemplateRedirect.aspx?template=/ContentManagerNet/MembersOnly.aspx&ContentID=' + convert(varchar(18), c.ContentID) + '"'    
        WHEN w.UseAspNetTemplateFlag = 'Y' AND c.MembersOnlyFlag = 'Y' AND c.OpenInNewWindowFlag = 'Y' AND @InUseNewWindowFlag = 'Y' THEN
           @VirtualDirectoryPath + '/' + n.Name +  + '/AM/ContentManagerNet/MembersOnly.aspx?Section=' + n.Name + '&ContentID=' + convert(varchar(18), c.ContentID) + @LeftNewWinString + @VirtualDirectoryPath + '/' + n.Name +  + '/AM/ContentManagerNet/MembersOnly.aspx?Section=' + n.Name + '&ContentID=' + convert(varchar(18), c.ContentID) + @RightNewWinString
        WHEN w.UseAspNetTemplateFlag = 'Y' AND c.MembersOnlyFlag = 'Y' THEN
           @VirtualDirectoryPath + '/' + n.Name +  + '/AM/ContentManagerNet/MembersOnly.aspx?Section=' + n.Name + '&ContentID=' + convert(varchar(18), c.ContentID) + '"'
        WHEN w.UseAspNetTemplateFlag = 'Y' AND n.NavContentGroupInd = 'C' AND c.OpenInNewWindowFlag = 'Y' AND @InUseNewWindowFlag = 'Y' AND isNULL(c.MembersOnlyFlag, 'N') != 'Y' THEN
           @GECodePath + 'ContentManagerNet/TemplateRedirect.aspx?' + 'template=/ContentManagerNet/HTMLDisplay.aspx&ContentID=' + convert(varchar(18), c.ContentID) + @LeftNewWinString + @GECodePath + 'ContentManagerNet/TemplateRedirect.aspx?'+ '?template=/ContentManagerNet/HTMLDisplay.aspx&ContentID=' + convert(varchar(18), c.ContentID) + @RightNewWinString
        WHEN w.UseAspNetTemplateFlag = 'Y' AND n.NavContentGroupInd = 'C' THEN
           @GECodePath + 'ContentManagerNet/TemplateRedirect.aspx?' +'template=/ContentManagerNet/HTMLDisplay.aspx&ContentID=' + convert(varchar(18), c.ContentID) + '"'
        WHEN c.OpenInNewWindowFlag = 'Y' AND @InUseNewWindowFlag = 'Y' AND isNULL(c.MembersOnlyFlag, 'N') != 'Y' THEN
           REPLACE(ISNULL(ch.PublishLocation, c.PublishLocation), REPLACE(IsNull(w.NavPublishDirectory,''),'\','/'), IsNull(w.NavContentDisplayPath,''))+ @LeftNewWinString + REPLACE(ISNULL(ch.PublishLocation, c.PublishLocation), REPLACE(IsNull(w.NavPublishDirectory,''),'\','/'), IsNull(w.NavContentDisplayPath,'')) + @RightNewWinString
        ELSE
           REPLACE(ISNULL(ch.PublishLocation, c.PublishLocation), REPLACE(IsNull(w.NavPublishDirectory,''),'\','/'), IsNull(w.NavContentDisplayPath,'')) + '"'
      END,
      c.Name,
      c.Keywords,
      c.Description,
      convert(varchar(20), IsNULL(c.PublicationDate, '01/01/1950'), 102),
      w.WebsiteKey,
      w.IncludeInCrossSiteSearchFlag,
      n.NavContentGroupInd,
      n.NavMenuID
    FROM
      vCurrent_Content c, Nav_Menu n,
      Content_HTML ch, Website w
    WHERE c.WorkflowStatusCode = 'P'
      and c.NavMenuID = n.NavMenuID
      and n.WebsiteKey <> @AdminWebsiteKey
      and c.ContentID = ch.ContentID
      and n.WebsiteKey = w.WebsiteKey
      and (LEN(RTrim(ch.PublishLocation)) > 0 OR LEN(RTrim(c.PublishLocation)) > 0)
      AND IsNull(n.HideFlag,'N') = 'N'

  /*
  ** Finally, after all rows have been added to the temp table, we need to
  ** generate the comma separated list of Interest Categories
  */
  
  DECLARE c_InterestCategory CURSOR FOR
    select cic.InterestCategoryID,
           cic.ComponentID,
           ic.Name
      from Component_Interest_Category cic, #temp t, Interest_Category ic
     where cic.ComponentCode = 'CM'
       and cic.ComponentID = t.ContentID
       and cic.InterestCategoryID = ic.InterestCategoryID
     order by cic.ComponentID

  OPEN c_InterestCategory
  
  FETCH NEXT FROM c_InterestCategory INTO
    @InterestCategoryID,
    @ComponentID,
    @InterestCategoryName

  SET @OldComponentID = @ComponentID
  SET @InterestCategoryList = @InterestCategoryID
  SET @InterestCategoryNameList = @InterestCategoryName
  SET @l_FirstRow = 1
    
  WHILE (@@FETCH_STATUS=0) BEGIN
    IF ( @OldComponentID != @ComponentID ) BEGIN
      UPDATE #temp
         SET InterestCategoryList = @InterestCategoryList,
             Keywords = Keywords + ' ' + Left(@InterestCategoryNameList, 1000 - Len(Keywords) - 1)
       WHERE ContentID = @OldComponentID

      SET @InterestCategoryList = @InterestCategoryID
      SET @InterestCategoryNameList = @InterestCategoryName
    END
    ELSE BEGIN
      IF ( @l_FirstRow = 1 ) BEGIN
        SET @InterestCategoryList = @InterestCategoryID
        SET @InterestCategoryNameList = @InterestCategoryName
        SET @l_FirstRow = 0
      END
      ELSE BEGIN
        SET @InterestCategoryList = @InterestCategoryList + ',' + convert(varchar(10), @InterestCategoryID)      
        SET @InterestCategoryNameList = @InterestCategoryNameList + ' ' + @InterestCategoryName      
      END
    END

    SET @OldComponentID = @ComponentID

    FETCH NEXT FROM c_InterestCategory INTO
      @InterestCategoryID,
      @ComponentID,
      @InterestCategoryName

  END

  UPDATE #temp
     SET InterestCategoryList = @InterestCategoryList,
         Keywords = Keywords + ' ' + Left(@InterestCategoryNameList, 1000 - Len(Keywords) - 1)
    WHERE ContentID = @OldComponentID

  CLOSE c_InterestCategory
  DEALLOCATE c_InterestCategory

  /* Do the similar thing for WebsiteKey */
  UPDATE #temp
     SET AllWebsiteKeys = Convert(varchar(50),WebsiteKey) + ','

  -- For each nav item, add Websites are marked to include in the search
  -- and only include those websites that has the setting on.
  DECLARE c_NavMenu CURSOR FOR
   SELECT DISTINCT b.NavMenuID, b.WebsiteKey
     FROM #temp a, Nav_Menu_Website b, Website c
    WHERE a.NavMenuID = b.NavMenuID
      AND a.WebsiteKey <> b.WebsiteKey
      AND b.WebsiteKey = c.WebsiteKey
      AND c.IncludeInCrossSiteSearchFlag = 'Y'

  OPEN c_NavMenu
  FETCH NEXT FROM c_NavMenu
   INTO @NavMenuID,
        @WebsiteKey
  WHILE @@FETCH_STATUS = 0 BEGIN
  
    UPDATE #temp
       SET AllWebsiteKeys = IsNull(AllWebsiteKeys,'') + Convert(varchar(50),@WebsiteKey) + ','
     WHERE NavMenuID = @NavMenuID

    FETCH NEXT FROM c_NavMenu
     INTO @NavMenuID,
          @WebsiteKey
  END
  CLOSE c_NavMenu
  DEALLOCATE c_NavMenu

  -- Remove extra ","
  UPDATE #temp
     SET AllWebsiteKeys = Left(AllWebsiteKeys,Len(AllWebsiteKeys)-1)
   WHERE AllWebsiteKeys IS NOT NULL

  /*
  ** Return our Result Set
  */

  SELECT t.FilePath,
         t.URL,
         t.Title,
         t.InterestCategoryList,
         t.Keywords,
         t.Description,
         Convert(Varchar(10), IsNull(t.ContentID,0)) + ',NCInd:' + NavContentGroupInd + ',WKEY:' + t.AllWebsiteKeys + ',' +  'CSEARCH:' + IsNull(IncludeInCrossSiteSearchFlag,'Y') + ',' + CustomField2 AS CustomField2,
         t.WebsiteKey,
         t.AllWebsiteKeys
    FROM #temp t

end

GO
GRANT EXECUTE ON  [dbo].[amsp_GetSearchableFiles] TO [IMIS]
GO